#!/usr/bin/env bash
cd `dirname $0`
source ../../../config.sh
source ../../basic/app/app_table_data_source.sh
exec_dir college_statistics_info

HIVE_DB=app
HIVE_TABLE=college_statistics_info
TARGET_TABLE=college_statistics_info
MODEL_DB=college_basic_info,student_score_record,basic_teacher_info,semester_year_report,basic_student_info,basic_semester_info,student_directed_education

function create_table() {
    hadoop fs -rm -r ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} || :
    hive -e "DROP TABLE IF EXISTS ${HIVE_DB}.${HIVE_TABLE};"
    hive -e "CREATE EXTERNAL TABLE IF NOT EXISTS ${HIVE_DB}.${HIVE_TABLE}(
	  semester_year String comment '学年 YYYY-YYYY',
	  statistics_type String comment '统计类型 占地面积ZDMJ等详见枚举',
	  statistics_name String comment '类型名称',
	  statistics_value String comment '统计值',
	  create_time String comment '创建时间'
	  
) COMMENT '学院--基本信息--学院概况统计'
    LOCATION '${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE}'"
    fn_log "创建表——学院--基本信息--学院概况统计：${HIVE_DB}.${HIVE_TABLE}"
}
function export_table() {
     clear_mysql_data "delete from ${TARGET_TABLE} where statistics_type = '${statistics_type}'
    and semester_year='${SEMESTER_YEAR}';"
    sqoop export --connect ${MYSQL_URL} --username ${MYSQL_USERNAME} --password ${MYSQL_PASSWORD} \
    --table ${TARGET_TABLE} --export-dir  ${BASE_HIVE_DIR}/${HIVE_DB}/${HIVE_TABLE} \
    --input-fields-terminated-by '\0001' --input-null-string '\\N' --input-null-non-string '\\N' \
    --null-string '\\N' --null-non-string '\\N' \
    --columns 'semester_year,statistics_type,statistics_name,statistics_value,create_time'
    fn_log "导出数据--学院--基本信息--学院概况统计:${HIVE_DB}.${TARGET_TABLE}"
}
function Area_covered(){

statistics_type=ZDMJ
create_table
	hive -e"
		INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		select
			'${SEMESTER_YEAR}' as semester_year,
			'${statistics_type}' as statistics_type,
			'占地面积' as statistics_name,
			cast(a.area_covered as decimal(9,2))as statistics_value,
			FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
			from 
			model.college_basic_info a
			where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 占地面积：${HIVE_DB}.${HIVE_TABLE}"

    export_table
}
function Architecture_acreage(){

statistics_type=JZMJ
create_table
	hive -e"
		INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		select
			'${SEMESTER_YEAR}' as semester_year,
			'${statistics_type}' as statistics_type,
			'建筑面积' as statistics_name,
			cast(a.architecture_acreage as decimal(9,3))as statistics_value,
			FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
			from 
			model.college_basic_info a
			where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 建筑面积：${HIVE_DB}.${HIVE_TABLE}"

    export_table
}
function Student_teacher_ratio() {

statistics_type=SSB
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
	select 
		'${SEMESTER_YEAR}' as semester_year,
		'${statistics_type}' as statistics_type,
		'生师比' as statistics_name,
		CONCAT(a.nums,':',b.number)as statistics_value,
		FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
		from
			(
			select count(a.code) as nums
			from
			model.student_score_record a
			where a.semester_year='${SEMESTER_YEAR}'
			)a,
			(
			select count(b.code) as number
			from 
			model.basic_teacher_info b
			where b.semester_year='${SEMESTER_YEAR}'
			)b
	"
	fn_log "导入数据 —— 生师比：${HIVE_DB}.${HIVE_TABLE}"

    export_table
}
function Portrait_arrivalmoney(){

statistics_type=HXJSFWDKE
create_table
	hive -e"
		INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		select
			'${SEMESTER_YEAR}' as semester_year,
			'${statistics_type}' as statistics_type,
			'纵向技术服务到款额' as statistics_name,
			cast(a.portrait_project_arrivalmoney as decimal(9,2)) as statistics_value,
			FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
			from
			model.semester_year_report a
			where a.semester_year='${SEMESTER_YEAR}'
			
	"
	fn_log "导入数据 —— 横向技术服务到款额：${HIVE_DB}.${HIVE_TABLE}"

    export_table
}
function Transverse_arrivalmoney(){
statistics_type=ZXKYJFDKE
create_table
	hive -e"
		INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		select
			'${SEMESTER_YEAR}' as semester_year,
			'${statistics_type}' as statistics_type,
			'横向科研经费到款额' as statistics_name,
			cast(a.transverse_service_arrivalmoney as decimal(9,2))as statistics_value,
			FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
			
			from
			model.semester_year_report a
			where a.semester_year='${SEMESTER_YEAR}'
			
	"
	fn_log "导入数据 —— 纵向科研经费到款额：${HIVE_DB}.${HIVE_TABLE}"

    export_table
}
function Administrative_acreage_avg(){

statistics_type=SJJXXZYF
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
	select
		'${SEMESTER_YEAR}' as semester_year,
		'${statistics_type}' as statistics_type,
		'生均教学行政用房' as statistics_name,
		IF ((A.administrative_acreage/B.STU_NUM) IS NULL,0,A.administrative_acreage/B.STU_NUM) as statistics_value,
		FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
		FROM MODEL.college_basic_info A
        LEFT JOIN
        (
            select count(distinct code) as stu_num,semester_year from app.basic_semester_student_info group by semester_year
        ) B
        ON A.semester_year=B.semester_year
		where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 生均教学行政用房：${HIVE_DB}.${HIVE_TABLE}"
	
	export_table
}
function Per_financial_allocation_level(){

statistics_type=SJCZBKSP
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		select
			'${SEMESTER_YEAR}' as semester_year,
			'${statistics_type}' as statistics_type,
			'生均财政拨款水平' as statistics_name,
			cast(a.financial_average as decimal(4,2)) as statistics_value,
			FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
			
			from
			model.semester_year_report a
			where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 生均财政拨款水平：${HIVE_DB}.${HIVE_TABLE}"
	
	export_table
}
function Number_of_network_multimedia_classrooms(){

statistics_type=WLDMTKSS
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
		select
			'${SEMESTER_YEAR}' as semester_year,
			'${statistics_type}' as statistics_type,
			'网络多媒体课室数' as statistics_name,
			a.multimedia_rooms as statistics_value,
			FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
			
			from
			model.semester_year_report a
			where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 网络多媒体课室数：${HIVE_DB}.${HIVE_TABLE}"
	
	export_table
}
function Practice_seat_avg(){

statistics_type=SJXNSJJXGWS
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
	select 
		'${SEMESTER_YEAR}' as semester_year,
		'${statistics_type}' as statistics_type,
		'生均校内实践教学工位数' as statistics_name,
		IF ((A.practice_acreage/B.STU_NUM) IS NULL,0,A.practice_acreage/B.STU_NUM) as statistics_value,
		FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
		FROM MODEL.college_basic_info A
        LEFT JOIN
        (
            select count(distinct code) as stu_num,semester_year from app.basic_semester_student_info group by semester_year
        ) B
        ON A.semester_year=B.semester_year
		where A.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 生均校内实践教学工位数：${HIVE_DB}.${HIVE_TABLE}"
	
	export_table
}
function Order_number_ratio(){

statistics_type=QYDDXSSZBL
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
	select
		'${SEMESTER_YEAR}' as semester_year,
		'${statistics_type}' as statistics_type,
		'企业订单学生所占比例' as statistics_name,
		concat(cast(a.n/b.s * 100 as decimal(9,2)),'','%') as statistics_value,
		FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
		from
		(
                        select
                        count(1) as n,
                        a.semester_year
                        FROM model.student_directed_education a
                        where a.type=1
                        group by semester_year
                    )a
                    left join
                    (
                        select
                        count(1) as s,
                        sem.semester_year as semester_year

                        FROM model.basic_student_info stu,model.basic_semester_info sem
                        group by sem.semester_year

                    ) b
                   on a.semester_year = b.semester_year
		where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 企业订单学生所占比例：${HIVE_DB}.${HIVE_TABLE}"
	
	export_table
}
function Scientific_instrument_total(){

statistics_type=SJJXKYYQSBZ
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
	select 
		'${SEMESTER_YEAR}' as semester_year,
		'${statistics_type}' as statistics_type,
		'生均教学科研仪器设备值' as statistics_name,
		IF ((A.scientific_instrument_total/B.STU_NUM) IS NULL,0,A.scientific_instrument_total/B.STU_NUM) as statistics_value,
		FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
		FROM MODEL.college_basic_info A
        LEFT JOIN
        (
            select count(distinct code) as stu_num,semester_year from app.basic_semester_student_info group by semester_year
        ) B
        ON A.semester_year=B.semester_year
		where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 生均教学科研仪器设备值：${HIVE_DB}.${HIVE_TABLE}"
	
	export_table
}
function Book_avg(){

statistics_type=SJTSSL
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
	select 
		'${SEMESTER_YEAR}' as semester_year,
		'${statistics_type}' as statistics_type,
		'生均图书数量' as statistics_name,
		IF ((A.paper_book/B.STU_NUM) IS NULL,0,A.paper_book/B.STU_NUM) AS statistics_value,
		FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
		FROM MODEL.college_basic_info A
        LEFT JOIN
        (
            select count(distinct code) as stu_num,semester_year from app.basic_semester_student_info group by semester_year
        ) B
        ON A.semester_year=B.semester_year
		where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 生均图书数量：${HIVE_DB}.${HIVE_TABLE}"
	
	export_table
}
function Increase_equipment_proportion(){

statistics_type=XZJXKYYQSBZB
create_table
	hive -e"
	INSERT INTO TABLE ${HIVE_DB}.${HIVE_TABLE}
	select 
		'${SEMESTER_YEAR}' as semester_year,
		'${statistics_type}' as statistics_type,
		'新增教学科研仪器设备占比' as statistics_name,
		concat(cast(a.add_equipment_ratio as decimal(4,2)),'%') as statistics_value,
		FROM_UNIXTIME(UNIX_TIMESTAMP()) AS create_time
		from
			model.semester_year_report a
			where a.semester_year='${SEMESTER_YEAR}'
	"
	fn_log "导入数据 —— 新增教学科研仪器设备占比：${HIVE_DB}.${HIVE_TABLE}"
	
	export_table
}
function getYearData(){
    vDate=`date +%Y`
    let vDate+=0;
    years=2
    for((i=1;i<=2;i++));
    do
      let NOW_YEAR=vDate-i+1
      let PRE_YEAR=vDate-i
      SEMESTER_YEAR=${PRE_YEAR}"-"${NOW_YEAR}
      
      echo $SEMESTER_YEAR
		Area_covered >> ${RUNLOG} 2>&1
		Architecture_acreage >> ${RUNLOG} 2>&1
		Student_teacher_ratio >> ${RUNLOG} 2>&1
		Portrait_arrivalmoney >> ${RUNLOG} 2>&1
		Transverse_arrivalmoney >> ${RUNLOG} 2>&1
		Administrative_acreage_avg >> ${RUNLOG} 2>&1
		Per_financial_allocation_level >> ${RUNLOG} 2>&1
		Number_of_network_multimedia_classrooms >> ${RUNLOG} 2>&1
		Practice_seat_avg >> ${RUNLOG} 2>&1
		Order_number_ratio >> ${RUNLOG} 2>&1
		Scientific_instrument_total >> ${RUNLOG} 2>&1
		Book_avg >> ${RUNLOG} 2>&1
		Increase_equipment_proportion >> ${RUNLOG} 2>&1 
    done
}
RUNLOG=./logs/$0_`date +%Y-%m-%d`.log 2>&1

if [ $# == 1 ]
        then
        getYearData
        data_cleaning
        finish
    else
        getYearData
        finish
    fi